package sqlexplorer.application;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Observable;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;

import sqlexplorer.application.exceptions.ConnectionFailedException;
import sqlexplorer.domain.DatabaseConnection;
import sqlexplorer.domain.DatabaseType;

public class ConnectionsController extends Observable{
	
	private static final String CONNECTION_TYPE="connectiontype";
	private static final String CONNECTION_NAME="connectionname";
	private static final String JDBC_URL="jdbcurl";
	private static final String USER_NAME="username";
	private static final String PASSWORD="password";
	
	private static ConnectionsController m_instance=null;
	
	private List<DatabaseConnection> m_connections;
	private DatabaseConnection m_activeDatabaseConnection;
	private Connection m_connection;
	
	private ConnectionsController(){
		m_connections=new ArrayList<DatabaseConnection>();
		m_activeDatabaseConnection=null;
		m_connection=null;
	}
	
	public static ConnectionsController getInstance(){
		if(m_instance==null){
			m_instance=new ConnectionsController();
		}
		
		return m_instance;
	}
	
	public void closeConnection() throws SQLException{
		if(m_connection!=null){
			try{
				m_connection.close();
				
				setChanged();
				notifyObservers("connectionCancelled");
			}catch(SQLException exc){
				exc.printStackTrace();
			}finally{
				m_connection=null;
			}
		}
	}
	
	public void connect(int index) throws sqlexplorer.application.exceptions.ConnectionFailedException{
		if((index>=0) && (index<m_connections.size())){
			try{
				//close an existing connection
				closeConnection();
				
				//build new connection
				m_connection=m_connections.get(index).connect();
				
				m_activeDatabaseConnection=m_connections.get(index);
				
				setChanged();
				notifyObservers("connectionChanged");
			}catch(sqlexplorer.domain.exceptions.ConnectionFailedException exc){
				sqlexplorer.application.exceptions.ConnectionFailedException cfe=new sqlexplorer.application.exceptions.ConnectionFailedException(exc.getMessage());
				cfe.setStackTrace(exc.getStackTrace());
				
				throw cfe;
			}catch(SQLException exc){
				sqlexplorer.application.exceptions.ConnectionFailedException cfe=new sqlexplorer.application.exceptions.ConnectionFailedException(exc.getMessage());
				cfe.setStackTrace(exc.getStackTrace());
				
				throw cfe;
			}
		}
	}
	
	public void connect(String connectionName) throws sqlexplorer.application.exceptions.ConnectionFailedException{
		if((connectionName!=null) && (connectionName.equals("")==false)){
			for(int i=0; i<m_connections.size(); i++){
				if(connectionName.equals(m_connections.get(i).getConnectionName())){
					connect(i);
					
					i=i+m_connections.size();
				}
			}
		}
	}
	
	public void createConnection(String url,String connectionName,String userName,String password,DatabaseType type){
		if((connectionName!=null) && (connectionName.equals("")==false) && (userName!=null) && (userName.equals("")==false) &&
				(password!=null) && (password.equals("")==false) && (url!=null) && (url.equals("")==false) && (isNameUnique(-1,connectionName))){
			m_connections.add(new DatabaseConnection(url,connectionName,userName,password,type));
			
			setChanged();
			notifyObservers("connectionAdded");
		}else{
			throw new InvalidParameterException();
		}
	}
	
	public void editConnection(int index,String url,String connectionName,String userName,String password,DatabaseType type){
		if((index>=0) && (index<m_connections.size())){
			if((connectionName!=null) && (connectionName.equals("")==false) && (userName!=null) && (userName.equals("")==false) &&
					(password!=null) && (password.equals("")==false) && (url!=null) && (url.equals("")==false) && (isNameUnique(index,connectionName))){
				boolean isActiveConnection=false;
				
				if((m_activeDatabaseConnection!=null) && (m_activeDatabaseConnection.getConnectionName().equals(m_connections.get(index)))){
					isActiveConnection=true;
				}
				
				m_connections.get(index).setJdbcUrl(url);
				m_connections.get(index).setConnectionName(connectionName);
				m_connections.get(index).setUserName(userName);
				m_connections.get(index).setPassword(password);
				m_connections.get(index).setDatabaseType(type);
				
				setChanged();
				notifyObservers("connectionAdded");
				
				if(isActiveConnection){
					try{
						closeConnection();
						connect(index);
					}catch(ConnectionFailedException exc){
						exc.printStackTrace();
					}catch(SQLException exc){
						exc.printStackTrace();
					}
				}
			}else{
				throw new InvalidParameterException();
			}
		}else{
			throw new InvalidParameterException();
		}
	}
	
	@SuppressWarnings("unchecked")
	public void exportConnections(String fileName) throws IOException{
		if((fileName!=null) && (fileName.equals("")==false) && (m_connections.size()>0)){
			//create the json
			JSONArray connArr=new JSONArray();
			
			for(DatabaseConnection connection:m_connections){
				JSONObject connObj=new JSONObject();
				
				connObj.put(ConnectionsController.CONNECTION_TYPE,connection.getDatabaseType().toString());
				connObj.put(ConnectionsController.CONNECTION_NAME,connection.getConnectionName());
				connObj.put(ConnectionsController.JDBC_URL,connection.getJdbcUrl());
				connObj.put(ConnectionsController.USER_NAME,connection.getUserName());
				connObj.put(ConnectionsController.PASSWORD,connection.getPassword());
				
				connArr.add(connObj);
				
			}
			
			//write the json
			BufferedOutputStream bos=null;
			
			try{
				bos=new BufferedOutputStream(new FileOutputStream(new File(fileName)));
				
				bos.write(connArr.toJSONString().getBytes());
			}catch(IOException exc){
				exc.printStackTrace();
				
				throw exc;
			}finally{
				try{
					bos.close();
				}catch(Exception exc){}
			}
		}else{
			throw new InvalidParameterException();
		}
	}
	
	public void importConnections(String fileName) throws IOException,ParseException{
		if((fileName!=null) && (fileName.equals("")==false)){
			BufferedReader br=null;
			
			try{
				br=new BufferedReader(new InputStreamReader(new FileInputStream(new File(fileName))));
				
				JSONParser jsonParser=new JSONParser();
				JSONArray connArr=(JSONArray)jsonParser.parse(br);
				
				if(connArr.size()>0){
					m_connections.clear();
					
					for(int i=0; i<connArr.size(); i++){
						JSONObject connObj=(JSONObject)connArr.get(i);
						
						DatabaseType type=DatabaseType.valueOf(DatabaseType.class,(String)connObj.get(ConnectionsController.CONNECTION_TYPE));
						String name=(String)connObj.get(ConnectionsController.CONNECTION_NAME);
						String url=(String)connObj.get(ConnectionsController.JDBC_URL);
						String userName=(String)connObj.get(ConnectionsController.USER_NAME);
						String password=(String)connObj.get(ConnectionsController.PASSWORD);
						
						m_connections.add(new DatabaseConnection(url,name,userName,password,type));
					}
					
					closeConnection();
					
					setChanged();
					notifyObservers("connectionAdded");
				}
			}catch(IOException exc){
				exc.printStackTrace();
				
				throw exc;
			}catch(ParseException exc){
				exc.printStackTrace();
				
				throw exc;
			}catch(SQLException exc){
				exc.printStackTrace();
			}finally{
				try{
					br.close();
				}catch(Exception exc){}
			}
		}else{
			throw new InvalidParameterException();
		}
	}
	
	private boolean isNameUnique(int index,String name){
		for(int i=0; i<m_connections.size(); i++){
			if(i!=index){
				if(m_connections.get(i).getConnectionName().equals(name)){
					return false;
				}
			}
		}
		
		return true;
	}
	
	public void removeConnection(int index){
		if((index>=0) && (index<m_connections.size())){
			if((m_activeDatabaseConnection!=null) &&
					(m_connections.get(index).getConnectionName().equals(m_activeDatabaseConnection.getConnectionName()))){
				try{
					closeConnection();
				}catch(SQLException exc){
					exc.printStackTrace();
				}
			}
			
			m_connections.remove(index);
			
			setChanged();
			notifyObservers("connectionAdded");
		}else{
			throw new InvalidParameterException();
		}
	}
	
	public DatabaseConnection getActiveDatabaseConnection(){
		return m_activeDatabaseConnection;
	}
	
	public Connection getConnection(){
		return m_connection;
	}
	
	public String[] getConnectionNames(){
		String[] names=new String[m_connections.size()];
		
		for(int i=0; i<names.length; i++){
			names[i]=m_connections.get(i).getConnectionName();
		}
		
		return names;
	}
	
	public DatabaseConnection getDatabaseConnection(int index){
		if((index>=0) && (index<m_connections.size())){
			return m_connections.get(index);
		}else{
			return null;
		}
	}
	
	public int getIndexForName(String name){
		if((name!=null) && (name.equals("")==false)){
			for(int i=0; i<m_connections.size(); i++){
				if(name.equals(m_connections.get(i).getConnectionName())){
					return i;
				}
			}
			
			return -1;
		}else{
			return -1;
		}
	}
}
